package com.eke.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.eke.bean.CourseType;
import com.jason.dbhelper2.jdbc.BaseJdbcTemplate;

@Repository("courseTypeDao")
public class CourseTypeDao extends BaseJdbcTemplate<CourseType>{
	
	/**
	 * 随机查询4个大课程分类
	 * @return List<{@link CourseType}>
	 */
	public List<CourseType> queryFourCourseType(){
		return this.queryForList("SELECT * FROM course_type where course_sub_type_id =1  ORDER BY  RAND() LIMIT 4");
	}
	
	/**
	 * 查询所有一级分类
	 * @return List<{@link CourseType}>
	 */
	public List<CourseType> queryAllBigCourseType(){
		return this.queryForList("select * from course_type where course_sub_type_id = 1");
	}
	
	/**
	 * 查询所有二级分类byId
	 * @param course_sub_type_id
	 * @return {@link List}<{@link CourseType}>
	 */
	public List<CourseType> queryAllTwoCoureType(Integer course_sub_type_id){
		return this.queryForList("SELECT * FROM course_type WHERE course_sub_type_id = ?",course_sub_type_id );
	}
	
	
	/**
	 * 查询所有二级分类
	 * @return {@link List}<{@link CourseType}>
	 */
	public List<CourseType> queryAllTwoCoureType(){
		return this.queryForList("select t2.course_type_id,t2.type_value,t2.course_sub_type_id FROM course_type as t1 LEFT JOIN course_type  as t2 ON t2.course_sub_type_id = t1.course_type_id  where t1.course_sub_type_id  = 1 ");
	}	
	
	/**
	 * 查询所有三级分类
	 * @return {@link List}<{@link CourseType}>
	 */
	public List<CourseType> queryAllThreeCourseType(){
		StringBuilder sb = new StringBuilder();
//		select t3.course_type_id,t3.type_value,t3.course_sub_type_id
//		FROM course_type as t1 
//		INNER JOIN course_type as t2 ON t2.course_sub_type_id = t1.course_type_id
//		INNER JOIN course_type as t3 ON t3.course_sub_type_id = t2.course_type_id
//		where t1.course_sub_type_id  = 1 
		sb.append("select t3.course_type_id,t3.type_value,t3.course_sub_type_id");
		sb.append(" FROM course_type as t1");
		sb.append(" INNER JOIN course_type as t2 ON t2.course_sub_type_id = t1.course_type_id");
		sb.append(" INNER JOIN course_type as t3 ON t3.course_sub_type_id = t2.course_type_id");
		sb.append(" where t1.course_sub_type_id  = 1 ");
		return this.queryForList(sb.toString());
	}
}
